﻿/*
************************************************************
CreateDate  *  Author        *  Content
2008-07-29  *  donne.yang  *  Query data and execute data
************************************************************
NO   *  UpdateDate   *  Author        *  Content

*************************************************************
*/
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.Odbc;
using System.Collections;
using System.Configuration;

namespace Stc.Dbo
{
    public class OdbcDBOpration
    {
        private string strConn;//DB連接字串

        public OdbcDBOpration()
        {
            if (Configuration.OdbcConn == null)
            {
                this.strConn = ConfigurationSettings.AppSettings["OdbcConn"];
                Configuration.OdbcConn = ConfigurationSettings.AppSettings["OdbcConn"];
            }
            else
            {
                this.strConn = Configuration.OdbcConn;
            }
        }

        public OdbcDBOpration(string strConn)
        {
            this.strConn = strConn;
        }

        /// <summary>
        /// Connect Oracle DB 
        /// </summary>
        /// <returns>Oracle DB Connection</returns>
        private OdbcConnection GetConnection()
        {
            OdbcConnection odbcConnection = null;
            try
            {
                odbcConnection = new OdbcConnection(strConn);
                odbcConnection.Open();
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return odbcConnection;
        }

        /// <summary>
        /// Close ODBC DB Connection
        /// </summary>
        /// <param name="odbcConnection">OdbcConnection</param>
        private void CloseDB(OdbcConnection odbcConnection)
        {
            if (odbcConnection.State != ConnectionState.Closed)
            {
                try
                {
                    odbcConnection.Close();
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
        }

        /// <summary>
        /// Query data from oracle DB
        /// </summary>
        /// <param name="strSql">sql sentence</param>
        /// <returns>data with dataset format</returns>
        public DataSet GetResult(string strSql)
        {
            OdbcConnection odbcConnection = GetConnection();

            DataSet dsData = new DataSet();

            try
            {
                OdbcDataAdapter odbcDataAdapter = new OdbcDataAdapter();

                odbcDataAdapter.SelectCommand = new OdbcCommand(strSql, odbcConnection);

                odbcDataAdapter.Fill(dsData);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                CloseDB(odbcConnection);
            }

            return dsData;
        }

        /// <summary>
        /// Query data from oracle DB
        /// </summary>
        /// <param name="strSql">sql sentence</param>
        /// <returns>data with datareader format</returns>
        public OdbcDataReader GetResultRead(string strSql)
        {
            OdbcConnection odbcConnection = GetConnection();

            OdbcDataReader odbcDataReader = null;

            try
            {
                OdbcCommand odbcCommand = new OdbcCommand(strSql, odbcConnection);

                odbcDataReader = odbcCommand.ExecuteReader();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                CloseDB(odbcConnection);
            }

            return odbcDataReader;
        }

        /// <summary>
        /// update data of odbc DB
        /// </summary>
        /// <param name="strSql">sql sentence</param>
        /// <returns>execute state with ture or false</returns>
        public bool UpdateData(string strSql)
        {
            bool blnFlag = false;

            OdbcConnection odbcConnection = GetConnection();

            try
            {
                OdbcCommand odbcCommand = new OdbcCommand(strSql, odbcConnection);

                if (odbcCommand.ExecuteNonQuery() > 0)
                {
                    blnFlag = true;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                CloseDB(odbcConnection);
            }

            return blnFlag;
        }

        /// <summary>
        /// update data of odbc DB with Parmeters
        /// </summary>
        /// <param name="strSql">sql sentence</param>
        /// <returns>execute state with ture or false</returns>
        public bool UpdateData(string strSql, OdbcParameter[] Parameters)
        {
            bool blnFlag = false;
            int num = 0;

            OdbcConnection odbcConnection = GetConnection();

            try
            {
                OdbcCommand odbcCommand = odbcConnection.CreateCommand();
                odbcCommand.CommandText = strSql;
                odbcCommand.CommandType = CommandType.Text;
                odbcCommand.Parameters.AddRange(Parameters);

                num = odbcCommand.ExecuteNonQuery();
                odbcCommand.Parameters.Clear();
                odbcCommand.Dispose();

                if (num > 0)
                {
                    blnFlag = true;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                CloseDB(odbcConnection);
            }

            return blnFlag;
        }

        /// <summary>
        /// Execute multiple sql sentences of update orcale DB
        /// </summary>
        /// <param name="arrSqls">sql sentences</param>
        /// <returns>execute state with ture or false</returns>
        public bool ExecuteTransaction(ArrayList arrSqls)
        {
            bool blnFlag = false;

            OdbcConnection odbcConnection = GetConnection();

            OdbcTransaction odbcTransaction = odbcConnection.BeginTransaction();
            OdbcCommand odbcCommand = odbcConnection.CreateCommand();
            odbcCommand.Transaction = odbcTransaction;

            try
            {
                for (int i = 0; i < arrSqls.Count; i++)
                {
                    odbcCommand.CommandText = (string)arrSqls[i];
                    odbcCommand.ExecuteNonQuery();
                }

                odbcTransaction.Commit();

                blnFlag = true;
            }
            catch (Exception ex)
            {
                try
                {
                    odbcTransaction.Rollback();
                }
                catch (Exception ex2)
                {
                    if (odbcTransaction.Connection != null)
                    {
                        throw ex2;
                    }
                }
                throw ex;
            }
            finally
            {
                CloseDB(odbcConnection);
            }

            return blnFlag;
        }
    }
}
